package com.wiiss.ams.dao;

import cn.hutool.core.text.StrBuilder;
import cn.hutool.core.util.ObjectUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.json.JSONObject;
import cn.hutool.log.StaticLog;
import com.wiiss.ams.util.Const;
import com.wiiss.ams.util.Exc;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.SQLException;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.stream.Collectors;
import java.util.stream.Stream;

public class MysqlDML {

    private static final int BATCH_INSERT_SIZE = 1000;
    private static final Logger log = LoggerFactory.getLogger(MysqlDML.class);

    public static List<JSONObject> get(String database, String sql, LocalDateTime from, LocalDateTime to) {
        try {
            return DbFactory.get(database)
                    .query(sql, Map.of("from", Const.DATE_TIME_FORMATTER.format(from), "to", Const.DATE_TIME_FORMATTER.format(to)))
                    .stream()
                    .map(JSONObject::new)
                    .toList();
        } catch (SQLException e) {
            throw new Exc(e, "mysql query failed");
        }
    }

    public static int deleteBefore(String database, String table, String byField, LocalDateTime beforeTime) {
        int res = 0;
        try {
            res = DbFactory.get(database).execute(StrUtil.format("""
                                                                 DELETE FROM {} WHERE {} < '{}'
                                                                 """, table, byField, Const.DATE_TIME_FORMATTER.format(beforeTime)));
        } catch (SQLException e) {
            throw new Exc(e, "delete failed");
        }
        return res;
    }

    public static int truncate(String database, String table) {
        AtomicInteger effectedRow = new AtomicInteger(0);
        try {
            DbFactory.get(database).tx(db -> {
                effectedRow.addAndGet(db.execute(StrUtil.format("""
                                                                TRUNCATE {}
                                                                """, table)));
            });
        } catch (SQLException e) {
            throw new Exc(e, "mysql truncates failed");
        }
        return effectedRow.get();
    }

    public static Integer batchInsert(String database, String table, List<JSONObject> jsonObjects) {
        if (jsonObjects.isEmpty()) {
            return 0;
        }

        // 组装 insert sql
        List<String> fields = jsonObjects.get(0).keySet().stream().toList();

        List<String> insertSqls = segment(jsonObjects, BATCH_INSERT_SIZE).stream()
                .map(list -> list.stream()
                        .map(e -> fields.stream().map(f -> adapt2Mysql(e, f)).collect(Collectors.joining(",", "(", ")")))
                        .collect(Collectors.joining(",", "INSERT INTO " + table + fields.stream().collect(Collectors.joining(",", " (", ") ")) + "VALUES ", ";")))
                .toList();

        AtomicInteger counter = new AtomicInteger(0);

        // insert
        try {
            DbFactory.get(database).tx(db -> {
                for (String sql : insertSqls) {
                    counter.addAndGet(db.execute(sql));
                }
            });
        } catch (SQLException e) {
            throw new Exc(e, "mysql batch insert failed");
        }

        return counter.get();
    }

    public static Integer batchInsertN(String database, String table, List<JSONObject> jsonObjects) {
        if (jsonObjects.isEmpty()) {
            return 0;
        }
        // 组装 insert sql
        List<String> fields = jsonObjects.getFirst().keySet().stream().toList();
        List<String> insertSqls = segment(jsonObjects, BATCH_INSERT_SIZE).stream()
                .map(list -> list.stream()
                        .map(e -> fields.stream().map(f -> adapt2MysqlN(e, f))
                                .collect(Collectors.joining(",", "(", ")")))
                        .collect(Collectors.joining(",", "INSERT INTO " + table + fields.stream()
                                .collect(Collectors.joining(",", " (", ") ")) + "VALUES ", ";")))
                .toList();

        AtomicInteger counter = new AtomicInteger(0);

        // insert
        try {
            DbFactory.get(database).tx(db -> {
                for (String sql : insertSqls) {
                    counter.addAndGet(db.execute(sql));
                }
            });
        } catch (SQLException e) {
            throw new Exc(e, "mysql batchInsertN failed");
        }

        return counter.get();
    }

    public static Integer batchUpdate(String database, String table, List<JSONObject> jsonObjects) {
        if (jsonObjects.isEmpty()) {
            return 0;
        }
        //分批次
        List<List<JSONObject>> segment = segment(jsonObjects, BATCH_INSERT_SIZE);
        AtomicInteger counter = new AtomicInteger(0);
        //遍历segment
        segment.forEach(seg -> {
            List<String> updateSqlList = seg.stream().map(it -> {
                // 创建一个StrBuilder用于拼接SQL语句
                StrBuilder sqlBuilder = new StrBuilder();
                // 拼接更新语句的基本结构
                sqlBuilder.append("UPDATE ").append(table).append(" SET");
                //得到需要更新的字段名
                List<String> field = it.keySet().stream().toList();
                //拼接需要更新的字段和数据
                sqlBuilder.append(field.stream().map(f -> {
                    String value = adapt2MysqlN(it, f);
                    return f + " = " + value;
                }).collect(Collectors.joining(", ", " ", " ")));
                // 拼接WHERE条件
                sqlBuilder.append("WHERE ").append("id").append(" = ").append("'").append(it.getStr("id")).append("'");
                return sqlBuilder.toString();
            }).toList();
            try {
                DbFactory.get(database).tx(db -> {
                    for (String sql : updateSqlList) {
                        counter.addAndGet(db.execute(sql));
                    }
                });
            } catch (SQLException e) {
                throw new Exc(e, "mysql batch update failed");
            }
        });

        return counter.get();
    }

    /**
	 * 先去重再插入
	 * @param database        数据库
	 * @param table           表
	 * @param indexedField    索引字段 用来判断数据的唯一性
	 * @param sortedTimeField 时间排序字段
	 * @param afterTime       时间条件, 不必每次查全量数据
	 * @param dataList        待插入数据(有重复)
	 * @author Vec 2024年4月3日 08:43:23
	 */
    public static Integer distinctAndBatchInsert(String database, String table, String indexedField, String sortedTimeField, LocalDateTime afterTime, List<JSONObject> dataList) {
        if (dataList.isEmpty()) {
            return 0;
        }

        List<String> existedIndexes = new ArrayList<>();
        try {
            existedIndexes
                    .addAll(DbFactory.get(database).query(StrUtil.format("""
                                                                         SELECT
                                                                             {}
                                                                         FROM
                                                                             {}
                                                                         WHERE
                                                                             {} >= '{}'
                                                                         """, indexedField, table, sortedTimeField, Const.DATE_TIME_FORMATTER.format(afterTime)), String.class));
        } catch (SQLException e) {
            StaticLog.error(e);
            throw new RuntimeException("mysql queries failed", e);
        }

        return batchInsert(database, table, dataList.stream().filter(v -> !existedIndexes.contains(v.getStr(indexedField))).toList());
    }

    /**
     * 将数据分段
     * @param  sources  数据源
     * @param  eachSize 每段大小
     * @return          分段后的新集合
     * @author          52291590 2022年11月17日 上午10:25:07
     */
    private static List<List<JSONObject>> segment(List<JSONObject> sources, int eachSize) {
        return Stream.iterate(0, i -> i < sources.size(), i -> i + eachSize)
                .map(i -> sources.size() - i >= eachSize ? sources.subList(i, i + eachSize) : sources.subList(i, sources.size()))
                .toList();
    }

    /**
     * 适配字段值 以符合mysql要求
     * @author 52291590 2022年11月18日 上午9:15:41
     */
    private static String adapt2Mysql(JSONObject e, String field) {
        Object o = e.get(field);
        if (o == null) {
            return "''";
        }
        if(o instanceof String s) {
            if (StrUtil.isBlank(s) || StrUtil.equals(s,"null")) {
                s = "0.01" ;
            }
        }

        // 特殊处理下时间
        return StrUtil.format("'{}'", field.toLowerCase().contains("time") ? formatTime(e.getStr(field)) : e.getStr(field));
    }

    /**
     * 适配字段值 以符合mysql要求
     * @author 52291590 2022年11月18日 上午9:15:41
     */
    private static String adapt2MysqlN(JSONObject e, String field) {
        Object o = e.get(field);
        if (ObjectUtil.isNull(o)) {
            return null;
        }

        // 特殊处理下时间
        return StrUtil.format("'{}'", field.toLowerCase().contains("time") ? formatTimeN(e.getStr(field)) : e.getStr(field));
    }


    /**
     * 解决毫秒问题 e.g "2022-11-22 16:45:40.0"
     * @author 52291590 2022年11月22日 下午5:04:38
     */
    private static String formatTime(String timeStr) {
        if (StrUtil.isNotBlank(timeStr) ) {
            int i = timeStr.indexOf(".");
            return i == -1 ? timeStr : timeStr.substring(0, i);
        }
        return "1970-01-01 00:00:00";
    }

    /**
     * 解决毫秒问题 e.g "2022-11-22 16:45:40.0"
     * @author 52291590 2022年11月22日 下午5:04:38
     */
    private static String formatTimeN(String timeStr) {
        if (StrUtil.isNotBlank(timeStr) ) {
            int i = timeStr.indexOf(".");
            return i == -1 ? timeStr : timeStr.substring(0, i);
        }
        return null;
    }

}
